/**
 * 
 */
package com.theanh.RssActivity.db;

import com.theanh.RssActivity.Store;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import com.theanh.RssActivity.entity.Category;
import com.theanh.RssActivity.entity.Channel;
import com.theanh.RssActivity.entity.Item;
import com.theanh.RssActivity.entity.Setting;
import com.theanh.RssActivity.ui.ListChannelScreen;
import com.theanh.RssActivity.util.Constants;
import com.theanh.RssActivity.util.Utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

/**
 * @author Nguyen Thanh Nam
 * 
 */
public class DbAdapter {

	private static final String DATABASE_NAME = "rss_db";
	private static final int DATABASE_VERSION = 2;
	private static final String TAG = "DbAdapter";
	private final Context context;
	private DatabaseHelper dbHelper;
	private SQLiteDatabase db;

	public DbAdapter(Context context) {
		this.context = context;
	}

	public void open() throws SQLException {
		dbHelper = new DatabaseHelper(context);
		db = dbHelper.getWritableDatabase();
	}

	public void close() {
		dbHelper.close();
		db.close();
	}

	public List<Category> getCategories() {
		Cursor cursor = db.query(CategoryTable.TABLE_NAME,
				CategoryTable.CULUMNS, null, null, null, null, null);
		List<Category> result = new ArrayList<Category>();
		if (cursor != null) {
			cursor.moveToFirst();
			for (int i = 0; i < cursor.getCount(); i++) {
				// Create category store this row
				Category category = new Category();
				category.setID(cursor.getInt(cursor
						.getColumnIndex(CategoryTable.ID)));
				category.setName(cursor.getString(cursor
						.getColumnIndex(CategoryTable.NAME)));
				category.setIconID(cursor.getInt(cursor
						.getColumnIndex(CategoryTable.ICON_ID)));

				// Add Category to list
				result.add(category);

				// Goto next row
				cursor.moveToNext();
			}

			cursor.close();
		}

		return result;
	}

	public long insertCategory(Category category) {
		ContentValues values = new ContentValues();
		values.put(CategoryTable.NAME, category.getName());
		values.put(CategoryTable.ICON_ID, category.getIconID());

		return db.insert(CategoryTable.TABLE_NAME, null, values);
	}

	public int updateCategory(Category category) {
		ContentValues values = new ContentValues();
		values.put(CategoryTable.NAME, category.getName());
		values.put(CategoryTable.ICON_ID, category.getIconID());

		return db.update(CategoryTable.TABLE_NAME, values, CategoryTable.ID
				+ "=" + category.getID(), null);
	}

	public boolean deleteCategory(Category category) {
		boolean result = db.delete(CategoryTable.TABLE_NAME, CategoryTable.ID
				+ "=" + category.getID(), null) > 0;

		List<Channel> channels = getChannels(category);
		for (Channel channel : channels) {
			deleteChannel(channel);
		}
		return result;
	}

	public List<Channel> getChannels(Category category) {
		String selection = ChannelTable.CATEGORY_ID + "=" + category.getID();
		Cursor cursor = db.query(ChannelTable.TABLE_NAME, ChannelTable.CULUMNS,
				selection, null, null, null, null);
		List<Channel> result = new ArrayList<Channel>();
		if (cursor != null) {
			cursor.moveToFirst();
			for (int i = 0; i < cursor.getCount(); i++) {
				// Create Channel store this row
				Channel channel = new Channel();
				channel.setID(cursor.getInt(cursor
						.getColumnIndex(ChannelTable.ID)));
				channel.setName(cursor.getString(cursor
						.getColumnIndex(ChannelTable.NAME)));
				channel.setUrl(cursor.getString(cursor
						.getColumnIndex(ChannelTable.URL)));
				channel.setLogoUrl(cursor.getString(cursor
						.getColumnIndex(ChannelTable.LOGO_URL)));
				channel.setCategoryID(cursor.getInt(cursor
						.getColumnIndex(ChannelTable.CATEGORY_ID)));

				// Add Channel to list
				result.add(channel);

				// Goto next row
				cursor.moveToNext();
			}

			cursor.close();
		}

		return result;
	}

	public long insertChannel(Channel channel) {
		ContentValues values = new ContentValues();
		values.put(ChannelTable.NAME, channel.getName());
		values.put(ChannelTable.URL, channel.getUrl());
		values.put(ChannelTable.LOGO_URL, channel.getLogoUrl() == null ? "''" : channel.getLogoUrl());
		values.put(ChannelTable.CATEGORY_ID, channel.getCategoryID());

		return db.insert(ChannelTable.TABLE_NAME, null, values);
	}

	public int updateChannel(Channel channel) {
		ContentValues values = new ContentValues();
		values.put(ChannelTable.NAME, channel.getName());
		values.put(ChannelTable.LOGO_URL, channel.getLogoUrl() == null ? "''" : channel.getLogoUrl());
		values.put(ChannelTable.URL, channel.getUrl());

		return db.update(ChannelTable.TABLE_NAME, values, ChannelTable.ID + "="
				+ channel.getID(), null);
	}

	public boolean deleteChannel(Channel channel) {
		boolean result = db.delete(ChannelTable.TABLE_NAME, ChannelTable.ID
				+ "=" + channel.getID(), null) > 0;
		db.delete(ItemTable.TABLE_NAME, ItemTable.CHANNEL_ID + "="
				+ channel.getID(), null);
		
		return result;
	}

	public List<Item> getItems(Channel channel) {
		return getItems(channel, true);
	}

	public List<Item> getItems(Channel channel, boolean containReaded) {
		String selection = ItemTable.CHANNEL_ID + "=" + channel.getID();
		if (!containReaded) {
			selection += " AND " + ItemTable.READED + "=0";
		}
		// String orderBy = ItemTable.ID + " DESC";
		String orderBy = null;
		Cursor cursor = db.query(ItemTable.TABLE_NAME, ItemTable.CULUMNS,
				selection, null, null, null, orderBy);
		List<Item> result = new ArrayList<Item>();
		if (cursor != null) {
			cursor.moveToFirst();
			for (int i = 0; i < cursor.getCount(); i++) {
				// Create Item store this row
				Item item = new Item();
				item.setID(cursor.getInt(cursor.getColumnIndex(ItemTable.ID)));
				item.setTitle(cursor.getString(cursor
						.getColumnIndex(ItemTable.TITLE)));
				item.setDescription(cursor.getString(cursor
						.getColumnIndex(ItemTable.DESCRIPTION)));
				item.setUrl(cursor.getString(cursor
						.getColumnIndex(ItemTable.URL)));
				long l = cursor.getLong(cursor
						.getColumnIndex(ItemTable.PUBLISH_DATE));
				item.setPublishDate(new Date(l));
				item.setAuthor(cursor.getString(cursor
						.getColumnIndex(ItemTable.AUTHOR)));
				item.setChannelID(cursor.getInt(cursor
						.getColumnIndex(ItemTable.CHANNEL_ID)));
				item
						.setReaded((cursor.getInt(cursor
								.getColumnIndex(ItemTable.READED))) == 0 ? false
								: true);
				item.setImageUrl(cursor.getString(cursor
						.getColumnIndex(ItemTable.IMAGE_URL)));

				// Add Category to list
				result.add(item);

				// Goto next row
				cursor.moveToNext();
			}

			cursor.close();
		}

		return result;
	}

	public List<Item> getItemsByDate(Channel channel, Date date) {
		List<Item> items = getItems(channel, true);
		List<Item> result = new ArrayList<Item>();
		for (Item item : items) {
//			if (item.getPublishDate().compareTo(date) >= 0)
			if (Utils.compareDate(item.getPublishDate(),date) == 0)
				result.add(item);
		}
		return result;
	}

	public List<Item> findItemByTitle(Channel channel, String title) {
		String selection = ItemTable.CHANNEL_ID + "=" + channel.getID()
				+ " AND " + ItemTable.TITLE + " LIKE '%" + title + "%';";
		Cursor cursor = db.query(ItemTable.TABLE_NAME, ItemTable.CULUMNS,
				selection, null, null, null, null);
		List<Item> result = new ArrayList<Item>();
		if (cursor != null) {
			cursor.moveToFirst();
			for (int i = 0; i < cursor.getCount(); i++) {
				// Create Item store this row
				Item item = new Item();
				item.setID(cursor.getInt(cursor.getColumnIndex(ItemTable.ID)));
				item.setTitle(cursor.getString(cursor
						.getColumnIndex(ItemTable.TITLE)));
				item.setDescription(cursor.getString(cursor
						.getColumnIndex(ItemTable.DESCRIPTION)));
				item.setUrl(cursor.getString(cursor
						.getColumnIndex(ItemTable.URL)));
				long l = cursor.getLong(cursor
						.getColumnIndex(ItemTable.PUBLISH_DATE));
				item.setPublishDate(new Date(l));
				item.setAuthor(cursor.getString(cursor
						.getColumnIndex(ItemTable.AUTHOR)));
				item.setChannelID(cursor.getInt(cursor
						.getColumnIndex(ItemTable.CHANNEL_ID)));
				item
						.setReaded((cursor.getInt(cursor
								.getColumnIndex(ItemTable.READED))) == 0 ? false
								: true);
				item.setImageUrl(cursor.getString(cursor
						.getColumnIndex(ItemTable.IMAGE_URL)));

				// Add Category to list
				result.add(item);

				// Goto next row
				cursor.moveToNext();
			}

			cursor.close();
		}

		return result;
	}

	public long insertItem(Item item) {
		if (item.getImageUrl() == null) {
			item.setImageUrl("");
		}
		
		ContentValues values = new ContentValues();
		values.put(ItemTable.TITLE, item.getTitle());
		values.put(ItemTable.DESCRIPTION, item.getDescription());
		values.put(ItemTable.URL, item.getUrl());
		values.put(ItemTable.PUBLISH_DATE, Utils.getLongValueDate(item
				.getPublishDate()));
		values.put(ItemTable.AUTHOR, item.getAuthor());
		values.put(ItemTable.CHANNEL_ID, item.getChannelID());
		values.put(ItemTable.IMAGE_URL, item.getImageUrl());

		return db.insert(ItemTable.TABLE_NAME, null, values);
	}

	public boolean deleteItem(Item item) {
		return db.delete(ItemTable.TABLE_NAME, ItemTable.ID + "="
				+ item.getID(), null) > 0;
	}

	public long getNumberCategory() {
		String sql = "SELECT COUNT(*) FROM Category";
		return simpleQuery(sql);
	}

	public long getNumberChannel(Category category) {
		String sql = "SELECT COUNT(*) FROM Channel WHERE CategoryID="
				+ category.getID();
		return simpleQuery(sql);
	}

	public long getNumberItem(Channel channel) {
		String sql = "SELECT COUNT(*) FROM Item WHERE ChannelID="
				+ channel.getID();
		return simpleQuery(sql);
	}

	public long getNumberItemUnRead(Channel channel) {
		String sql = "SELECT COUNT(*) FROM Item WHERE ChannelID="
				+ channel.getID() + " AND Readed=0;";
		return simpleQuery(sql);
	}

	public boolean checkCategoryExist(Category category) {
		String sql = "SELECT COUNT(*) FROM Category WHERE Name='"
				+ category.getName() + "';";
		return simpleQuery(sql) > 0 ? true : false;
	}

	public boolean checkChannelExist(Channel channel) {
		String sql = "SELECT COUNT(*) FROM Channel WHERE Url='"
				+channel.getUrl()+"';";
		return simpleQuery(sql) > 0 ? true : false;
	}

	public boolean checkItemExist(Item item) {
		String sql = "SELECT COUNT(*) FROM Item WHERE Url='" + item.getUrl()
				+ "';";
		return simpleQuery(sql) > 0 ? true : false;
	}

	public boolean checkSettingExist() {
		String sql = "SELECT COUNT(*) FROM Setting;";
		return simpleQuery(sql) > 0 ? true : false;
	}

	public void setItemReaded(Item item) {
		int state = item.isReaded() ? 1 : 0;
		String sql = "UPDATE Item SET Readed=" + state + " WHERE ItemID="
				+ item.getID() + ";";
		Log.i(TAG, sql);
		simpleExecute(sql);
	}

	// public void setItemsReaded(List<Item> items) {
	// int state = item.isReaded() ? 1 : 0;
	// String itemIDs = "(";
	// for(int i = 0; i < items.size()-1; i++ ){
	// itemIDs += item[i].getID() + ",";
	// }
	// itemIDs += items[items.size()-1] + ")";
	//		 
	// String sql = "UPDATE Item SET Readed=" + state + " WHERE ItemID IN"
	// + itemIDs + ";";
	// Log.i(TAG, sql);
	// simpleExecute(sql);
	// }
	public Setting getSetting() {
		Cursor cursor = db.query(SettingTable.TABLE_NAME, SettingTable.CULUMNS,
				null, null, null, null, null);
		Setting setting = new Setting();
		if (cursor != null) {
			cursor.moveToFirst();
			setting
					.setID(cursor
							.getInt(cursor.getColumnIndex(SettingTable.ID)));
			setting.setAutoUpdate((cursor.getInt(cursor
					.getColumnIndex(SettingTable.AUTO_UPDATE))) == 0 ? false
					: true);
			setting.setHideReaded((cursor.getInt(cursor
					.getColumnIndex(SettingTable.HIDE_READED))) == 0 ? false
					: true);
			setting.setDisplayImage((cursor.getInt(cursor
					.getColumnIndex(SettingTable.DISPLAY_IMAGE))) == 0 ? false
					: true);
			setting.setItemExpired(cursor.getInt(cursor
					.getColumnIndex(SettingTable.ITEM_EXPXPIRED)));
			setting.setPageSize(cursor.getInt(cursor
					.getColumnIndex(SettingTable.PAGE_SIZE)));
			setting.setAutoCheckUrl((cursor.getInt(cursor
					.getColumnIndex(SettingTable.AUTO_CHECK_URL))) == 0 ? false
					: true);
			cursor.close();
		}
		return setting;
	}

	public int updateSetting(Setting setting) {
		ContentValues values = new ContentValues();
		values.put(SettingTable.AUTO_UPDATE, setting.isAutoUpdate());
		values.put(SettingTable.DISPLAY_IMAGE, setting.isDisplayImage());
		values.put(SettingTable.HIDE_READED, setting.isHideReaded());
		values.put(SettingTable.ITEM_EXPXPIRED, setting.getItemExpired());
		values.put(SettingTable.PAGE_SIZE, setting.getPageSize());
		values.put(SettingTable.AUTO_CHECK_URL, setting.isAutoCheckUrl());
		return db.update(SettingTable.TABLE_NAME, values, SettingTable.ID + "="
				+ setting.getID(), null);
	}

	public long simpleQuery(String sql) {
		SQLiteStatement statement = db.compileStatement(sql);
		long result = statement.simpleQueryForLong();
		// Remove resource
		statement.close();
		return result;
	}

	public void simpleExecute(String sql) {
		SQLiteStatement statement = db.compileStatement(sql);
		statement.execute();
		statement.close();
	}

	public void deleteItemExpired(int expired) {
		Date date = Calendar.getInstance().getTime();
		date.setDate(date.getDate() - expired);
		List<Item> items = getItems();
		if (items == null || items.size() == 0)
			return;

		for (Item item : items) {
			if (date.compareTo(item.getPublishDate()) > 0) {
				deleteItem(item);
			}
		}
	}

	public List<Item> getItems() {
		String selection = null;
		// String orderBy = ItemTable.ID + " DESC";
		String orderBy = null;
		Cursor cursor = db.query(ItemTable.TABLE_NAME, new String[] {
				ItemTable.ID, ItemTable.PUBLISH_DATE }, selection, null, null,
				null, orderBy);
		List<Item> result = new ArrayList<Item>();
		if (cursor != null) {
			cursor.moveToFirst();
			for (int i = 0; i < cursor.getCount(); i++) {
				// Create Item store this row
				Item item = new Item();
				item.setID(cursor.getInt(cursor.getColumnIndex(ItemTable.ID)));

				long l = cursor.getLong(cursor
						.getColumnIndex(ItemTable.PUBLISH_DATE));
				item.setPublishDate(new Date(l));

				// Add Category to list
				result.add(item);

				// Goto next row
				cursor.moveToNext();
			}

			cursor.close();
		}

		return result;
	}

	public void deleteTables() {
		db.execSQL("DROP TABLE IF EXISTS " + CategoryTable.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + ChannelTable.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + ItemTable.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + SettingTable.TABLE_NAME);
	}

	public void createTables() {
		Log.i("DbAdapter", "Create DB");
		db.execSQL(CategoryTable.SQL_CREATE);
		db.execSQL(ChannelTable.SQL_CREATE);
		db.execSQL(ItemTable.SQL_CREATE);
		db.execSQL(SettingTable.SQL_CREATE);
	}

	public static String constraintFK(String constraintName, String columName,
			String foreignTable, String foreignColumName) {
		String format = "CONSTRAINT %1$s FOREIGN KEY (%2$s) REFERENCES %3$s (%4$s) ON DELETE CASCADE, "
				+ "CONSTRAINT %1$s FOREIGN KEY (%2$s) REFERENCES %3$s (%4$s) ON UPDATE CASCADE";
		String constraint = String.format(format, constraintName, columName,
				foreignTable, foreignColumName);
		return constraint;
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.i("DbAdapter", "Create DB");

			db.execSQL(CategoryTable.SQL_CREATE);
			db.execSQL(ChannelTable.SQL_CREATE);
			db.execSQL(ItemTable.SQL_CREATE);
			db.execSQL(SettingTable.SQL_CREATE);

			insertSetting(db);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + CategoryTable.TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + ChannelTable.TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + ItemTable.TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + SettingTable.TABLE_NAME);

			onCreate(db);
		}

		private void insertSetting(SQLiteDatabase db) {
			// Check setting exited
			String sql = "SELECT COUNT(*) FROM Setting;";
			SQLiteStatement statement = db.compileStatement(sql);
			long result = statement.simpleQueryForLong();
			// Remove resource
			statement.close();

			if (result == 0) {
				// Add setting default
				Setting setting = new Setting();
				ContentValues values = new ContentValues();
				values.put(SettingTable.ID, setting.getID());
				values.put(SettingTable.AUTO_UPDATE, setting.isAutoUpdate());
				values
						.put(SettingTable.DISPLAY_IMAGE, setting
								.isDisplayImage());
				values.put(SettingTable.HIDE_READED, setting.isHideReaded());
				values.put(SettingTable.ITEM_EXPXPIRED, setting
						.getItemExpired());
				values.put(SettingTable.PAGE_SIZE, setting.getPageSize());
				values.put(SettingTable.AUTO_CHECK_URL, setting
						.isAutoCheckUrl());
				db.insert(SettingTable.TABLE_NAME, null, values);
			}
		}
	}
}
